package com.group3.merchant.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.group3.entity.Dish;
import com.group3.merchant.dao.IDishDao;
import com.group3.merchant.utils.DbUtils;

public class DishDaoImpl implements IDishDao {

	@Override
	public List<Dish> queryAllDish() {
		String sql = "select * from dish";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = DbUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Dish dish = new Dish();
				dish.setdId(rs.getString(1));
				dish.setdName(rs.getString(2));
				dish.setdPrice(rs.getDouble(3));
				dish.setdImageURL(rs.getString(4));
				dish.setmId(rs.getString(5));
				dish.setpId(rs.getString(6));
				list.add(dish);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, rs);
		}
		return list;
	}

	@Override
	public List<Dish> queryByMId(String mid) {
		String sql = "select * from dish where mid=?";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = DbUtils.getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, mid);
			rs = ps.executeQuery();
			while (rs.next()) {
				Dish dish = new Dish();
				dish.setdId(rs.getString(1));
				dish.setdName(rs.getString(2));
				dish.setdPrice(rs.getDouble(3));
				dish.setdImageURL(rs.getString(4));
				dish.setmId(rs.getString(5));
				dish.setpId(rs.getString(6));
				list.add(dish);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, rs);
		}
		return list;
	}

	@Override
	public Dish insertDish(Dish dish) {
		String sql = "insert into dish values(?,?,?,?,?,?)";
		Connection conn = DbUtils.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dish.getdId());
			ps.setString(2, dish.getdName());
			ps.setString(3, String.valueOf(dish.getdPrice()));
			ps.setString(4, dish.getdImageURL());
			ps.setString(5, dish.getmId());
			ps.setString(6, dish.getpId());
			int lines = ps.executeUpdate();
			if (lines != 0) {
				return dish;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, null);
		}
		return null;
	}

	@Override
	public Dish updateDish(Dish dish) {
		String sql = "update Dish set name=?,price=?,imageurl=?,pid = ? where did=?";
		Connection conn = DbUtils.getConnection();
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dish.getdName());
			ps.setString(2, String.valueOf(dish.getdPrice()));
			ps.setString(3, dish.getdImageURL());
			ps.setString(4, dish.getpId());
			ps.setString(5, dish.getdId());
			int lines = ps.executeUpdate();
			System.out.println("Dao update Dish: " + lines);
			if (lines == 1) {
				return dish;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, null);
		}
		return null;
	}

	@Override
	public int deleteDish(Dish dish) {
		String sql = "delete from dish where did = ?";
		Connection conn = DbUtils.getConnection();
		PreparedStatement ps = null;
		int lines = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dish.getdId());
			lines = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, null);
		}
		return lines;
	}

	@Override
	public Dish queryBydId(String did) {
		String sql = "select * from dish where did=?";
		List<Dish> list = new ArrayList<Dish>();
		Connection conn = DbUtils.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Dish dish = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, did);
			rs = ps.executeQuery();
			while (rs.next()) {
				dish = new Dish();
				dish.setdId(rs.getString(1));
				dish.setdName(rs.getString(2));
				dish.setdPrice(rs.getDouble(3));
				dish.setdImageURL(rs.getString(4));
				dish.setmId(rs.getString(5));
				dish.setpId(rs.getString(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, rs);
		}
		return dish;
	}

	@Override
	public List<Dish> queryBy(String mid, String pid) {
		List<Dish> list = new ArrayList<Dish>();
		String sql = "select * from dish where mid=? and pid=?";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = DbUtils.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, mid);
			ps.setString(2, pid);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String dId = rs.getString("did");
				String dName = rs.getString("name");
				double dPrice = Double.parseDouble(rs.getString("price"));
				String dImageURL = rs.getString("imageURL");
				String mId = rs.getString("mid");
				String pId = rs.getString("pid");
				list.add(new Dish(dId, dName, dPrice, dImageURL, mid, pId));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(conn, ps, null);
		}

		return list;
	}
}
